﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Dapper;
using WeiXinPF.Common;
using WeiXinPF.DBUtility;
using WeiXinPF.Model;

namespace WeiXinPF.DAL
{
    public partial class wx_shop_product_comment
    {
        /// <summary>
        /// 添加评论
        /// </summary>
        /// <param name="model"></param>
        public int SaveComment(Model.wx_shop_product_comment model)
        {
            IDbConnection db = null;
            IDbTransaction trans = null;
            try
            {
                db = DbFactory.GetOpenedConnection();
                if (db.State != ConnectionState.Open)
                {
                    db.Open();
                }

                trans = db.BeginTransaction();

                string sql1 = @"Insert Into [dbo].[wx_shop_product_comment]
                                    ([OpenId],[orderId],[BusinessId],[BusinessType],[ProductId],[SkuId],[Content],[Score],[CreateTime],[Rank],[Status],[Auditor],[Username])
                                Values
                                    (@OpenId,@orderId,@BusinessId,@BusinessType,@ProductId,@SkuId,@Content,@Score,@CreateTime,@Rank,@Status,@Auditor,@Username);
                                Select @Id=Scope_Identity();
                                ";
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.AddDynamicParams(model);
                dynamicParameters.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
                db.Execute(sql1, dynamicParameters, trans);

                int id = dynamicParameters.Get<int>("@Id");

                if (model.Images != null && model.Images.Any())
                {
                    string sql2 = @"Insert Into [dbo].[wx_shop_product_commentimage]
                                        ([CommentId],[Path],[ThumbPath])
                                    Values
                                        (@CommentId, @Path, @ThumbPath)";

                    model.Images.ForEach(image =>
                    {
                        image.CommentId = id;
                        db.Execute(sql2, image, trans);
                    });
                }

                trans.Commit();

                return id;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
            }
            finally
            {
                if (db != null && db.State == ConnectionState.Open)
                {
                    db.Close();
                }
            }

            return 0;
        }

        /// <summary>
        /// 根据产品id和订单id获得是否已评价，如果规格id为0则代表不存在
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="productid"></param>
        /// <param name="skuId"></param>
        /// <returns></returns>
        public bool Exists(int orderId, int productid)
        {
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                string query = "Select Count(1) From dbo.wx_shop_product_comment Where OrderId=@orderId and productid = @productid ";

                return db.ExecuteScalar<int>(query, new { orderId, productid }) != 0;
            }
        }


        /// <summary>
        /// 取某个产品的平均分(不含规格)
        /// </summary>
        ///  /// <param name="productid"></param>
        public dynamic GetAverageComment(int productid)
        {
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                string tableName = "wx_shop_product_comment";

                if (string.IsNullOrEmpty(tableName))
                {
                    throw new Exception("参数不正确");
                }
                string query = string.Format(@"Select 
    b.productName As Name, Round(Avg(Score),1) As AvgScore,Count(a.ProductId) as UserCount 
 From wx_shop_product_comment a
 Inner Join wx_shop_product b
 On a.ProductId=b.id
 Where a.ProductId=@productid
 Group By a.ProductId,b.productName");
                return db.Query(query, new { ProductId = productid }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据分页和审核状态取得所选商品的评论
        /// </summary>
        /// <param name="productid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        public List<Model.wx_shop_product_comment> GetApprovedCommentsByProductId(int pageIndex, int pageSize, int productid, AduitStatus status, out int totalCount)
        {
            if (productid < 1)
            {
                throw new Exception("参数不正确");
            }

            string query = string.Format(@"Select a.*, b.productName From wx_shop_product_comment a
Inner Join wx_shop_product b
On b.id = a.productid
left join wx_shop_sku c on a.skuid = c.id
Where a.productid = @productid ");
            if (status != AduitStatus.所有)
            {
                query += " and a.status = " + ((int)status).ToString();
            }
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                var filter = new { ProductId = productid };
                totalCount = db.Query<int>(PagingHelper.CreateCountingSql(query), filter).FirstOrDefault();
                List<Model.wx_shop_product_comment> comments =
                    db.Query<Model.wx_shop_product_comment>(
                        PagingHelper.CreatePagingSql(totalCount, pageSize, pageIndex, query, "CreateTime Desc"), filter
                        ).ToList();

                if (comments.Any())
                {
                    comments.ForEach(comment =>
                    {
                        comment.Images =
                            db.Query<wx_shop_product_commentimage>("Select * From dbo.wx_shop_product_commentimage Where CommentId=@Id",
                                new { comment.Id }).ToList();
                        comment.Images.ForEach(img =>
                        {
                            img.Path = MyCommFun.getAppSettingValue("fileServerBasePath") + img.Path;
                            img.ThumbPath = MyCommFun.getAppSettingValue("fileServerBasePath") + img.ThumbPath;
                        });
                    });
                }

                return comments;
            }
        }


        /// <summary>
        /// 分页取得所有评论
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        public List<Model.wx_shop_product_comment> GetAllCommentsbyPage(int pageIndex, int pageSize, out int totalCount)
        {
            string query = string.Format(@"Select a.*, b.productName,c.attributeId as skuName From wx_shop_product_comment a
Inner Join wx_shop_product b
On b.id = a.productid
left join wx_shop_sku c on a.skuid = c.id  ");
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                totalCount = db.Query<int>(PagingHelper.CreateCountingSql(query)).FirstOrDefault();
                List<Model.wx_shop_product_comment> comments =
                    db.Query<Model.wx_shop_product_comment>(
                        PagingHelper.CreatePagingSql(totalCount, pageSize, pageIndex, query, "CreateTime Desc")
                        ).ToList();

                if (comments.Any())
                {
                    comments.ForEach(comment =>
                    {
                        comment.Images =
                            db.Query<wx_shop_product_commentimage>("Select * From dbo.wx_shop_product_commentimage Where CommentId=@Id",
                                new
                                {
                                    comment.Id
                                }).ToList();
                        comment.Images.ForEach(img =>
                        {
                            img.Path = MyCommFun.getAppSettingValue("fileServerBasePath") + img.Path;
                            img.ThumbPath = MyCommFun.getAppSettingValue("fileServerBasePath") + img.ThumbPath;
                        });
                    });
                }
                return comments;
            }
        }



        /// <summary>
        /// 根据状态分页取得所有的评论
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        public List<Model.wx_shop_product_comment> GetApproveCommentsByPage(int pageIndex, int pageSize, AduitStatus status, out int totalCount)
        {
            string statussql = status == AduitStatus.所有 ? "" : " Where a.Status = @status ";
            string query = string.Format(@"Select a.*, b.productName,c.attributeId as skuName From wx_shop_product_comment a
Inner Join wx_shop_product b
On b.id = a.productid
left join wx_shop_sku c on a.skuid = c.id " + statussql);
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                var filter = new { Status = (int)status };
                totalCount = db.Query<int>(PagingHelper.CreateCountingSql(query), filter).FirstOrDefault();
                List<Model.wx_shop_product_comment> comments =
                    db.Query<Model.wx_shop_product_comment>(
                        PagingHelper.CreatePagingSql(totalCount, pageSize, pageIndex, query, "CreateTime Desc"), filter
                        ).ToList();

                if (comments.Any())
                {
                    comments.ForEach(comment =>
                    {
                        comment.Images =
                            db.Query<wx_shop_product_commentimage>("Select * From dbo.wx_shop_product_commentimage Where CommentId=@Id",
                                new
                                {
                                    comment.Id
                                }).ToList();
                        comment.Images.ForEach(img =>
                                                {
                                                    img.Path = MyCommFun.getAppSettingValue("fileServerBasePath") + img.Path;
                                                    img.ThumbPath = MyCommFun.getAppSettingValue("fileServerBasePath") + img.ThumbPath;
                                                });
                    });
                }
                return comments;
            }
        }


        /// <summary>
        /// 根据评价和状态分页取得所有的评论
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="ranklevel"></param>
        /// <param name="status"></param>
        /// <param name="totalCount"></param>
        public List<Model.wx_shop_product_comment> GetCommentOnRankAndStatusByPage(int pageIndex, int pageSize, int ProductId, RankLevel ranklevel, AduitStatus status, out int totalCount)
        {
            string sql_rank = "";
            switch (ranklevel)
            {
                case RankLevel.全部:
                    break;
                case RankLevel.好评:
                    sql_rank = " and score in (4,5) ";
                    break;
                case RankLevel.中评:
                    sql_rank = " and score in (2,3) ";
                    break;
                case RankLevel.差评:
                    sql_rank = " and score in (1) ";
                    break;
                default:
                    break;
            }
            string statussql = status == AduitStatus.所有 ? "" : " and a.Status = @status ";
            string query = string.Format(@"Select a.*, b.productName,c.attributeId as skuName From wx_shop_product_comment a
Inner Join wx_shop_product b
On b.id = a.productid 
left join wx_shop_sku c on a.skuid = c.id where a.ProductId = @ProductId " + statussql + sql_rank);
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                var filter = new { Status = (int)status, ProductId = ProductId };
                totalCount = db.Query<int>(PagingHelper.CreateCountingSql(query), filter).FirstOrDefault();
                List<Model.wx_shop_product_comment> comments =
                    db.Query<Model.wx_shop_product_comment>(
                        PagingHelper.CreatePagingSql(totalCount, pageSize, pageIndex, query, "CreateTime Desc"), filter
                        ).ToList();

                if (comments.Any())
                {
                    comments.ForEach(comment =>
                    {
                        comment.Images =
                            db.Query<wx_shop_product_commentimage>("Select * From dbo.wx_shop_product_commentimage Where CommentId=@Id",
                                new
                                {
                                    comment.Id
                                }).ToList();
                        comment.Images.ForEach(img =>
                        {
                            img.Path = MyCommFun.getAppSettingValue("fileServerBasePath") + img.Path;
                            img.ThumbPath = MyCommFun.getAppSettingValue("fileServerBasePath") + img.ThumbPath;
                        });
                    });
                }
                return comments;
            }
        }


        /// <summary>
        /// 审核某条评论，评论id，审核结果，审核人
        /// </summary>
        /// <param name="commentid"></param>
        /// <param name="status"></param>
        /// <param name="Auditor"></param>
        public void ApproveComment(int commentid, AduitStatus status, int Auditor)
        {
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                int status_ = (int)status;
                string query = "update dbo.wx_shop_product_comment set status=@status_ , Auditor = @Auditor Where ID=@commentid";
                db.Execute(query, new { commentid, status_, Auditor });
            }
        }

        /// <summary>
        /// 删除某条评论
        /// </summary>
        /// <param name="commentid"></param>
        public void DeleteComment(int commentid)
        {
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                string query = "delete from dbo.wx_shop_product_comment Where ID=@commentid";
                db.Execute(query, new { commentid });
            }
        }

        /// <summary>
        /// 更新某条评论
        /// </summary>
        /// <param name="model"></param>
        public void UpdateComment(Model.wx_shop_product_comment model)
        {
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                string content = model.Content;
                int score = model.Score;
                int rank = model.Rank;
                DateTime CreateTime = DateTime.Now;
                int commentid = model.Id;
                string query = "update dbo.wx_shop_product_comment set content=@content , score = @score , rank = @rank , CreateTime = @CreateTime Where ID=@commentid";
                db.Execute(query, new { content, score, rank, CreateTime, commentid });
            }
        }

        /// <summary>
        /// 通过所有未审核的评论
        /// </summary>
        public void ApproveAllComment(int Auditor)
        {
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                string query = "update dbo.wx_shop_product_comment set status=2,Auditor = @Auditor Where status=1";
                db.Execute(query, new { Auditor });
            }
        }

        /// <summary>
        /// 获取评论统计数据，包括总共多少评论、好评数、中评数及差评数等
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public wx_shop_product_comment_stat GetProductCommentStatistics(int ProductId)
        {
            

            string sql_GetProductCommentStatistics = @"select p.id as ProductId,p.productName,t1.GoodCount,t1.MediumCount,t1.NegativeCount from wx_shop_product p left join
(select c1.ProductId,COUNT(c2.Score) as GoodCount,COUNT(c3.Score) as MediumCount,COUNT(c4.Score) as NegativeCount  from wx_shop_product_comment c1
left join wx_shop_product_comment c2 on c1.ProductId = c2.ProductId and c1.Id=c2.id and c2.Score in (4,5)
left join wx_shop_product_comment c3 on c1.ProductId = c3.ProductId and c1.Id=c3.id and c3.Score in (2,3)
left join wx_shop_product_comment c4 on c1.ProductId = c4.ProductId and c1.Id=c4.id and c4.Score in (1)
where c1.ProductId=@ProductId
group by c1.ProductId
)as t1
on p.id = t1.ProductId
where p.id=@ProductId";
            var filter = new { ProductId = ProductId };
            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                List<Model.wx_shop_product_comment_stat> model =
                    db.Query<Model.wx_shop_product_comment_stat>(
                        PagingHelper.CreatePagingSql(1, 1, 1, sql_GetProductCommentStatistics, "ProductId Desc"), filter
                        ).ToList();
                if (model.Count > 0)
                {
                    model[0].TotalCount = model[0].GoodCount + model[0].MediumCount + model[0].NegativeCount;
                    return model[0];
                }
                return null;
            }
        }
    }
}
